using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace System {
	public class DAL {
		public string lastSql="";
		//public static string Csv(DataTable tab,string sep) {
		//  StringBuilder sb=new StringBuilder();
		//  int cntc=0;
		//  string[] cols=new string[tab.Columns.Count];
		//  foreach(DataColumn col in tab.Columns)
		//    cols[cntc++]=col.ColumnName;
		//  sb.AppendLine(string.Join(sep,cols));
		//  foreach(DataRow row in tab.Rows)
		//  {
		//    string[] arr=new string[row.ItemArray.Length];
		//    int cnt=0;
		//    foreach(object obj in row.ItemArray)
		//    {
		//      if(obj is Byte[])
		//        arr[cnt++]=Encoding.ASCII.GetString(obj as Byte[]).Trim();
		//      else
		//        arr[cnt++]=obj.ToString().Trim();
		//    }
		//    sb.AppendLine(string.Join(sep,arr));
		//  }
		//  return sb.ToString();
		//}
		public static string Csv(DataTable tab) {
			StringBuilder sw=new StringBuilder();
			int cntc=0;
			string[] cols=new string[tab.Columns.Count];
			if(tab.Rows.Count>0)
			{
				foreach(DataColumn col in tab.Columns)
					cols[cntc++]=col.ColumnName;
				sw.AppendLine(string.Join(",",cols));
				foreach(DataRow row in tab.Rows)
				{
					string[] arr=new string[row.ItemArray.Length];
					int cnt=0;
					foreach(object obj in row.ItemArray)
						arr[cnt++]=obj.ToString().Trim();
					sw.AppendLine(string.Join(",",arr));
				}
			}
			return sw.ToString();

		}

		public static void Csv(DataTable tab,string FileName) {
			File.WriteAllText(FileName,Csv(tab));
			//using(StreamWriter sw=File.CreateText(FileName))
			//{
			//  int cntc=0;
			//  string[] cols=new string[tab.Columns.Count];
			//  if(tab.Rows.Count>0)
			//  {
			//    foreach(DataColumn col in tab.Columns)
			//      cols[cntc++]=col.ColumnName;
			//    sw.WriteLine(string.Join(",",cols));
			//    foreach(DataRow row in tab.Rows)
			//    {
			//      string[] arr=new string[row.ItemArray.Length];
			//      int cnt=0;
			//      foreach(object obj in row.ItemArray)
			//        arr[cnt++]=obj.ToString().Trim();
			//      sw.WriteLine(string.Join(",",arr));
			//    }
			//  }
			//}
		}

		public static string CsvColumn(DataTable tab,string colName,bool quote) {
			StringBuilder sb=new StringBuilder();
			List<string> vals=new List<string>(tab.Rows.Count);
			foreach(DataRow row in tab.Rows)
				//if(!vals.Contains(row[colName].ToString()))
				vals.Add(row[colName].ToString());
			if(quote)
				return string.Concat("'",string.Join("','",vals.ToArray()),"'");
			else
				return string.Join(",",vals.ToArray());
		}
		string _cs;
		public DAL() : this("server=(local);integrated security=sspi;connection timeout=1") { }
		public DAL(params string[] args) {
			if(args.Length==2)
				_cs=string.Format("server={0};database={1};integrated security=sspi;connection timeout=1",args);
			else
				throw new Exception();
		}
		public DAL(string cs) { _cs=cs; }
		public DataTable Query(string sql,params object[] args) {
			string qry=string.Format(sql,args);
			lastSql=qry;
			DataTable ret=new DataTable();
			using(SqlDataAdapter adp=new SqlDataAdapter(qry,_cs))
			{
				adp.SelectCommand.CommandTimeout=0;
				adp.Fill(ret);
			}
			return ret;
		}
		public DataSet QuerySet(string sql,params object[] args) {
			string qry=string.Format(sql,args);
			DataSet ret=new DataSet();
			using(SqlDataAdapter adp=new SqlDataAdapter(qry,_cs))
			{
				adp.SelectCommand.CommandTimeout=180;
				adp.Fill(ret);
			}
			return ret;
		}

		public int NonQry(string sql,params object[] args) {
			string qry=sql;
			if(args.Length!=0)
				qry=string.Format(sql,args);
			lastSql=qry;
			int ret=-1;
			using(SqlConnection con=new SqlConnection(_cs))
			using(SqlCommand cmd=new SqlCommand(qry,con))
			{
				cmd.CommandTimeout=100;
				con.Open();
				ret=cmd.ExecuteNonQuery();
				con.Close();
			}
			//Console.WriteLine(ret);
			return ret;
		}

		public object Scalar(string sql,params object[] args) {
			//System.Diagnostics.Stopwatch sw=System.Diagnostics.Stopwatch.StartNew();
			string qry=string.Format(sql,args);
			lastSql=qry;
			object ret=null;
			using(SqlConnection con=new SqlConnection(_cs))
			using(SqlCommand cmd=new SqlCommand(qry,con))
			{
				//Console.WriteLine(con.ConnectionTimeout);
				con.Open();
				ret=cmd.ExecuteScalar();
				con.Close();
			}
			//Console.WriteLine("------"+sw.Elapsed);
			return ret;
		}


	}
}
